Case Study 1 - Danny's Diner

Introduction:

Danny seriously loves Japanese food so in the beginning of 2021, he decides to embark upon a risky venture and opens up a cute little restaurant that sells his 3 favourite foods: sushi, curry and ramen.

Danny’s Diner is in need of your assistance to help the restaurant stay afloat - the restaurant has captured some very basic data from their few months of operation but have no idea how to use their data to help them run the business.

Problem Statement

Danny wants to use the data to answer a few simple questions about his customers, especially about their visiting patterns, how much money they’ve spent and also which menu items are their favourite. Having this deeper connection with his customers will help him deliver a better and more personalised experience for his loyal customers.

He plans on using these insights to help him decide whether he should expand the existing customer loyalty program - additionally he needs help to generate some basic datasets so his team can easily inspect the data without needing to use SQL.

Danny has provided you with a sample of his overall customer data due to privacy issues - but he hopes that these examples are enough for you to write fully functioning SQL queries to help him answer his questions!

Danny has shared with me 3 key datasets for this case study:

• sales
• menu
• members

Example Datasets

All datasets exist within the dannys_diner database schema

Table 1 : Sales

The sales table captures all customer_id level purchases with an corresponding order_date and product_id information for when and what menu items were ordered.

customer_id	order_date	product_id
A	2021-01-01	1
A	2021-01-01	2
A	2021-01-07	2
A	2021-01-10	3
A	2021-01-11	3

Table 2: menu

The menu table maps the product_id to the actual product_name and price of each menu item.

product_id	product_name	price
1	sushi	10
2	curry	15
3	ramen	12

Table 3: members

The final members table captures the join_date when a customer_id joined the beta version of the Danny’s Diner loyalty program.

customer_id	join_date
A	2021-01-07
B	2021-01-09

Case Study Questions

1. What is the total amount each customer spent at the restaurant?

The total amount spent by each customer at the restaurant is as follows:

• Customer A spent a total of $76 at the restaurant.
• Customer B spent a total of $74 at the restaurant.
• Customer C spent a total of $36 at the restaurant.

Detailed Explanation

Click to view

Customer segmentation:

Knowing the total spending of each customer allows the restaurant to segment its customer base based on their spending habits. This information can help identify high-value customers who contribute significantly to the restaurant's revenue. The restaurant can then tailor specific marketing campaigns or loyalty programs to retain and attract such customers.

Menu optimization:

By analyzing the spending patterns of customers, the restaurant can gain insights into which menu items are most popular and generate higher revenue. This information can be used to optimize the menu, ensuring that the most profitable and in-demand dishes are prioritized while considering the profitability of individual items.

Upselling and cross-selling opportunities:

Understanding the total spending of each customer enables the restaurant staff to identify opportunities for upselling and cross-selling.

For example, if a customer consistently spends a certain amount on their visits, the staff can recommend additional menu items or suggest higher-priced options to increase the average transaction value.

Personalized marketing and promotions:

By analyzing individual customer spending, the restaurant can create personalized marketing campaigns and promotions.

For example, customers who spend more can be offered exclusive discounts or rewards to encourage repeat visits. On the other hand, customers who spend less can be targeted with promotions to entice them to spend more.

Customer satisfaction and loyalty:

The total spending of each customer can be an indicator of their satisfaction level with the restaurant's offerings. By monitoring and analyzing this data, the restaurant can identify customers who may be at risk of churning or decreasing their spending. Proactive measures can then be taken to address any issues and improve customer satisfaction, thereby fostering loyalty and retaining valuable customers.

Short Explanation (Summary)

Click to view

The knowledge of the total amount each customer spent at the restaurant is crucial for gaining insights into customer spending behavior, identifying high-value customers, evaluating marketing efforts, and providing personalized experiences. It helps the restaurant make data-driven decisions to enhance customer satisfaction, drive revenue growth, and build long-term relationships with its clientele.

2. How many days has each customer visited the restaurant?

The number of times each customer visited the restaurant is as follows:

• Customer A visited the restaurant 4 times.
• Customer B visited the restaurant 6 times.
• Customer C visited the restaurant 2 times.

Detailed Explanation

Click to view

Customer segmentation:

The information allows the business to segment customers based on their visit frequency. This segmentation helps identify loyal customers who visit frequently and can be targeted with special offers or loyalty programs to enhance their satisfaction and encourage repeat visits.

Resource allocation:

Knowing the number of visits per customer helps with resource allocation and staffing decisions. It allows the business to anticipate busy periods and allocate staff accordingly to provide excellent service during peak times when frequent customers are likely to visit.

Customer retention:

Monitoring the visit frequency helps in identifying customers who may be at risk of churning or decreasing their visits. The business can proactively reach out to these customers, understand their concerns, and take measures to improve their experience, ultimately fostering customer loyalty and retention.

Marketing strategies:

The visit frequency data can guide marketing strategies and promotional campaigns. Customers who visit less frequently can be targeted with personalized offers or incentives to encourage them to visit more often. In contrast, frequent customers can be rewarded for their loyalty, driving customer satisfaction and fostering a positive brand image.

Menu and service optimization:

By analyzing visit frequency, the business can gain insights into customer preferences and adjust the menu or service offerings accordingly. It allows for menu optimization, highlighting popular dishes and removing less popular ones to better cater to customer preferences and increase overall satisfaction.

Short Explanation (Summary)

Click to view

This information helps understand customer engagement and loyalty, allowing for tailored marketing strategies and improved resource management. It aids in evaluating the effectiveness of efforts to attract repeat visits and measure customer retention. Overall, it contributes to enhancing the dining experience and optimizing restaurant operations.

3. What was the first item from the menu purchased by each customer?

The first orders of each customer are as follows:

• Customer A's first orders are curry and sushi.
• Customer B's first order is curry.
• Customer C's first order is ramen.

Detailed Explanation

Click to view

Understanding customer preferences:

By identifying the first orders of each customer, the restaurant can gain insights into their initial preferences. This information helps understand the types of dishes or specific menu items that attract customers during their first visit. It can be useful in tailoring marketing messages or promotions to highlight similar dishes and attract new customers.

Menu optimization:

Analyzing the first orders of customers allows the restaurant to assess the popularity of different menu items. By identifying which dishes are frequently chosen as the first order, the restaurant can ensure that these items are well-prepared, prominently displayed on the menu, and given appropriate emphasis in marketing materials. It helps optimize the menu to showcase items that have a high likelihood of creating a positive first impression on customers.

Personalized recommendations:

Knowing the first orders of each customer provides an opportunity to offer personalized recommendations for future visits. The restaurant can use this information to suggest complementary dishes or similar items that align with the customer's initial preferences. Personalized recommendations enhance the customer experience, encourage exploration of the menu, and increase the likelihood of repeat visits.

Upselling and cross-selling opportunities:

Understanding the first orders of customers can also help identify upselling and cross-selling opportunities. By analyzing the specific dishes chosen during the first visit, the restaurant can recommend additional items that pair well with those choices or suggest higher-priced options that align with the customer's initial preferences. This approach can increase the average transaction value and overall revenue.

Customer retention and loyalty:

The first order of a customer sets the tone for their dining experience and can greatly impact their decision to return. By monitoring and analyzing this data, the restaurant can identify any patterns or trends that influence customer retention. It allows for proactive measures to be taken to ensure customer satisfaction, address any issues, and encourage repeat visits, thus fostering customer loyalty.

Short Explanation (Summary)

Click to view

Knowing the first orders of customers can help in understanding their initial preferences, optimizing the menu, providing personalized recommendations, identifying upselling opportunities, and fostering customer retention and loyalty. It allows the restaurant to create a positive first impression, tailor the dining experience, and increase the chances of long-term customer engagement.

4. What is the most purchased item on the menu and how many times was it purchased by all customers?

The most purchased item on the menu is ramen, with a total of 8 purchases.

Detailed Explanation

Click to view

Inventory and supply management:

Knowing the most popular menu item allows the business to streamline its inventory and supply management processes. It ensures that an adequate stock of ingredients or products related to the popular item is maintained, minimizing the risk of running out of stock and maximizing customer satisfaction.

Menu optimization:

Understanding the most purchased item helps optimize the menu. The business can highlight or promote the popular item more prominently, potentially increasing its sales even further. It also provides insights into customer preferences, allowing for adjustments to the menu offerings to align with customer demand.

Profitability and cost management:

The most purchased item often generates significant revenue for the business. By identifying it, the business can assess its profitability and adjust pricing or portion sizes accordingly. It helps in maximizing profitability while ensuring competitive pricing and managing costs associated with the popular item.

Marketing and promotions:

The most purchased item can be used as a focal point in marketing campaigns and promotions. Highlighting the popular item in advertisements or offering special deals related to it can attract new customers and incentivize existing customers to make repeat purchases. It serves as a key marketing asset to drive customer engagement and increase sales.

Menu diversification and innovation:

While the most purchased item is essential, it also provides an opportunity to explore menu diversification and innovation. By understanding customer preferences, the business can introduce new variations or complementary dishes to enhance the overall dining experience and cater to a wider range of tastes. This helps in staying competitive, attracting new customers, and encouraging repeat visits.

Short Explanation (Summary)

Click to view

Identifying the most purchased item on the menu is beneficial for the business in multiple ways. It helps streamline inventory management, optimize the menu, maximize profitability, and guide marketing efforts. It also enables menu diversification and innovation to cater to customer preferences and drive business growth.

5. Which item was the most popular for each customer?

The first orders of each customer are as follows:

• Customer A and C’s favourite item is ramen.
• Customer B enjoys all items on the menu.

Detailed Explanation

Click to view

Customer satisfaction:

By identifying each customer's favorite item, the business can ensure customer satisfaction and enhance their dining experience. The ability to cater to individual preferences increases the likelihood of repeat visits and positive word-of-mouth recommendations.

Menu optimization:

Understanding the popularity of specific items among customers helps the business optimize its menu offerings. It provides insights into the demand for different dishes, allowing for adjustments to highlight customer favorites and potentially introducing variations or new dishes based on their preferences.

Personalized marketing and promotions:

Knowing each customer's favorite item enables the business to create personalized marketing campaigns and promotions. Targeted offers, discounts, or special menus featuring their preferred items can be tailored to attract and retain customers.

Customer loyalty and retention:

By recognizing and catering to individual preferences, the business can build customer loyalty. Offering their favorite item consistently enhances their connection with the brand, increasing the chances of customer retention and positive reviews.

Insights for future menu planning:

Identifying a customer who enjoys all items on the menu, like Customer B, can provide valuable insights for future menu planning and development. It indicates a versatile and adventurous palate, which can inform decisions regarding new menu additions or seasonal offerings.

Short Explanation (Summary)

Click to view

Identifying each customer's favorite item helps enhance customer satisfaction, optimize the menu, personalize marketing efforts, foster loyalty, and gain insights for future menu planning. It tailors the dining experience, promotes customer retention, and enables the business to cater to individual preferences effectively.

6. Which item was purchased first by the customer after they became a member?

The first orders of each customer after becoming meber are as follows:

• Customer A’s first order as a member is ramen.
• Customer B’s first order as a member is sushi.

Detailed Explanation

Click to view

Membership engagement:

By knowing the first order made by each customer after becoming a member, the business can assess the level of engagement and the initial preferences of new members. This knowledge can help tailor personalized communication and offers to further engage and retain these customers.

Member satisfaction:

Understanding the first order can provide an indication of member satisfaction. If the first order aligns with popular or highly-rated menu items, it suggests that the members are enjoying their dining experience. This knowledge can guide the business in maintaining the quality of the menu items that attract new members.

Menu optimization:

Analyzing the first orders can assist in menu optimization efforts. If certain menu items consistently appear as first orders for members, it indicates their popularity and potential for increased sales. The business can emphasize these items on the menu, feature them in promotions, or consider introducing variations or complementary dishes to further capitalize on their popularity.

Personalized member experiences:

The knowledge of the first order enables the business to provide personalized member experiences. It can help in tailoring recommendations, offers, or exclusive benefits based on the initial preferences of the members. This personalization strengthens member loyalty and increases the likelihood of repeat visits.

Insights for new member acquisition:

Understanding the first order preferences of members can provide insights for targeting new member acquisition. By identifying popular menu items among new members, the business can highlight these items in marketing campaigns or member acquisition strategies, attracting customers who have a high likelihood of becoming loyal members.

Short Explanation (Summary)

Click to view

Analyzing the first orders of members helps the business understand member preferences, tailor personalized experiences, optimize the menu, and enhance member engagement. It provides insights for targeted marketing, improves member satisfaction, and drives business growth.

7. Which item was purchased just before the customer became a member?

• Both customers’ last order before becoming members are sushi.

Detailed Explanation

Click to view

Customer insights:

Understanding the last orders before membership provides insights into the preferences of customers during their transition to membership. It helps the business understand the types of dishes or specific menu items that attract potential members and can guide efforts to appeal to similar tastes.

Membership benefits:

By knowing the last orders, the business can align membership benefits with customer preferences. For example, if sushi was the popular choice, the business can offer exclusive discounts or promotions on sushi-related items to entice new members and strengthen their initial membership experience.

Menu optimization:

Analyzing the last orders helps optimize the menu by identifying popular items before customers became members. It allows the business to highlight or promote these items more prominently, ensuring their continued availability and quality. It also helps guide menu expansion or variation to cater to customer preferences.

Member retention:

Recognizing the last orders made by customers before becoming members helps in member retention efforts. By continuing to offer their preferred items, the business can ensure a seamless transition to membership and maintain customer satisfaction. It increases the likelihood of repeat visits and long-term loyalty.

Marketing strategies:

The knowledge of customers' last orders can inform marketing strategies aimed at acquiring new members. By emphasizing similar menu items or offering promotions related to their previous orders, the business can attract potential members who have already shown interest in those items.

Short Explanation (Summary)

Click to view

The understanding the last orders before membership provides valuable insights for customer preferences, membership benefits, menu optimization, member retention, and targeted marketing strategies. It helps the business create appealing membership experiences, optimize the menu offerings, and attract new members effectively.

8. What is the total items and amount spent for each member before they became a member?

Before becoming members

• Customer A spent $25 on 2 items.
• Customer B spent $40 on 3 items.

Detailed Explanation

Click to view

Member benefits and promotions:

By knowing the customers' pre-membership purchase history, the business can tailor member benefits and promotions to align with their previous spending patterns. This personalized approach enhances the value proposition of membership and encourages customers to join based on their prior investment in the business.

Membership pricing and incentives:

Analyzing the total sales and items purchased can guide the pricing and incentive structure for membership programs. It helps determine the level of discounts, rewards, or exclusive offers that would be enticing for customers who have already demonstrated a certain level of spending before becoming members.

Member segmentation:

The data allows for customer segmentation based on pre-membership spending. Customers who have made higher-value purchases or bought a greater number of items can be identified as potentially higher-value members. This segmentation can guide personalized marketing efforts and enable targeted strategies to maximize their satisfaction and loyalty.

Membership ROI evaluation:

Comparing the total sales and items purchased before and after customers became members helps evaluate the return on investment (ROI) of the membership program. It provides insights into the impact of membership on customer spending and allows the business to assess the effectiveness of the program in driving increased sales and customer engagement.

Customer retention and loyalty:

Understanding pre-membership spending helps in retaining and fostering loyalty among customers who have already invested in the business. By recognizing their prior purchases and providing tailored experiences, the business can strengthen the relationship with these customers, encourage repeat visits, and increase the likelihood of long-term loyalty.

Short Explanation (Summary)

Click to view

Understanding the pre-membership spending of customers helps in tailoring member benefits, pricing, and incentives. It enables segmentation, evaluates membership ROI, and enhances customer retention strategies. By leveraging this data, businesses can personalize member experiences and maximize customer engagement and loyalty.

9. If each $1 spent equates to 10 points and sushi has a 2x points multiplier - how many points would each customer have?

The total points for Customers A, B and C are $860, $940 and $360.

Detailed Explanation

Click to view

Customer loyalty and rewards:

The total points accumulated by customers serve as a measure of their loyalty and engagement with the business. It provides a basis for implementing rewards programs or loyalty tiers based on point thresholds. Offering incentives or exclusive benefits based on points encourages repeat purchases and strengthens customer loyalty.

Personalized marketing and promotions:

Knowing the total points for each customer allows for personalized marketing and promotions. Customers with higher point balances can be targeted with exclusive offers or discounts to incentivize them to redeem their points and make additional purchases. This tailored approach enhances the customer experience and fosters ongoing engagement.

Customer segmentation:

The total points can be used as a basis for customer segmentation. Customers with higher point balances can be identified as high-value or VIP customers. This segmentation enables targeted marketing efforts, priority access to new offerings, or personalized communications to further nurture these valuable customer relationships.

Data-driven decision making:

Analyzing the total points provides valuable insights into customer behavior and preferences. It helps in understanding which products contribute to higher point accumulations, which can inform inventory management, pricing strategies, and product promotion decisions. This data-driven approach ensures that resources are allocated to maximize customer satisfaction and drive sales.

Performance evaluation:

The total points can be used to evaluate the effectiveness of loyalty and rewards programs. By comparing point balances over time, the business can assess program engagement, redemption rates, and overall customer satisfaction. This evaluation enables adjustments and improvements to the loyalty program to better align with customer expectations.

Short Explanation (Summary)

Click to view

Understanding the total points earned by customers based on their purchases helps drive customer loyalty, enables personalized marketing, informs customer segmentation, supports data-driven decision making, and facilitates program evaluation. It enhances customer engagement, encourages repeat purchases, and fosters long-term loyalty.

10. In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi - how many points do customer A and B have at the end of January?

• Customer A has 1,370 points.
• Customer B has 820 points.

Detailed Explanation

Click to view

Rewards and loyalty programs:

The points earned by customers provide a basis for implementing rewards and loyalty programs. Customers can be incentivized to accumulate more points and redeem them for special offers, discounts, or exclusive perks. This encourages repeat purchases and fosters customer loyalty.

Personalized marketing and promotions:

Knowing the points earned by customers allows for personalized marketing and promotions. Customers with higher point balances can be targeted with tailored offers or rewards that align with their preferences and purchasing behavior. This customized approach enhances customer engagement and increases the likelihood of continued patronage.

Member retention and satisfaction:

Understanding the points earned by customers who are members helps in member retention efforts. By recognizing their point accumulation and providing targeted benefits or incentives, the business can strengthen the relationship with these customers and increase their satisfaction. This contributes to higher member retention rates and enhances the overall membership experience.

Data-driven decision making:

Analyzing the points earned provides valuable insights into customer behavior and preferences. It helps identify popular menu items or promotions that generate higher point accumulation. This information can guide inventory management, pricing strategies, and promotional decisions to optimize customer satisfaction and drive sales.

Program evaluation and improvement:

Tracking the points earned allows for the evaluation of the effectiveness of rewards and loyalty programs. By analyzing point balances and redemption rates, the business can assess program engagement and customer satisfaction. This evaluation enables the identification of areas for improvement and adjustments to maximize program effectiveness and customer engagement.

Short Explanation (Summary)

Click to view

Understanding customer points earned based on purchases and membership helps drive loyalty, personalize marketing, retain members, make data-driven decisions, and evaluate program effectiveness. It enhances engagement, fosters satisfaction, and drives business growth through targeted benefits and improved customer experiences.

11. creating basic data tables that Danny and his team can use to quickly derive insights without needing to join the underlying tables using SQL.

Recreate the following table output using the available data:

Customer ID	Order Date	Product Name	Price	Member
A	2021-01-01	curry	15	N
A	2021-01-01	sushi	10	N
A	2021-01-07	curry	15	Y
A	2021-01-10	ramen	12	Y
A	2021-01-11	ramen	12	Y

12. Danny also requires further information about the ranking of customer products, but he purposely does not need the ranking for non-member purchases so he expects null ranking values for the records when customers are not yet part of the loyalty program.

Recreate the following table output using the available data:

Customer ID	Order Date	Product Name	Price	Member	Ranking
A	2021-01-01	curry	15	N	null
A	2021-01-01	sushi	10	N	null
A	2021-01-07	curry	15	Y	1
A	2021-01-10	ramen	12	Y	2
A	2021-01-11	ramen	12	Y	3
A	2021-01-11	ramen	12	Y	3

Creating basic data tables that provide insights without the need to join underlying tables using SQL can greatly help Danny and his team in the following ways:

Detailed Explanation

Click to view

Easy data access:

The data tables provide a consolidated view of the relevant information, making it easier and quicker for Danny and his team to access the insights they need. Instead of writing complex SQL queries every time, they can directly refer to the pre-built tables.

Streamlined analysis:

By having the necessary data already organized in tables, Danny and his team can perform analysis and derive insights more efficiently. They can apply filters, sorting, grouping, and other operations directly on the tables, saving time and effort.

Quick decision-making:

The availability of pre-built data tables allows for faster decision-making. Danny and his team can access the required information promptly, enabling them to make informed decisions and take actions promptly without the need for extensive SQL queries or data manipulations.

Improved productivity:

With pre-built data tables, Danny and his team can focus on analyzing the data and extracting insights rather than spending time writing complex SQL queries or joining tables. This improves overall productivity and efficiency in generating insights and executing data-driven tasks.

Reproducibility and consistency:

The data tables ensure consistency in the information provided, as they are pre-built and based on reliable data sources. This allows for reproducibility of analysis, making it easier to track and compare insights over time.

Short Explanation (Summary)

Click to view

Creating pre-built data tables for quick insights eliminates the need for complex SQL joins, streamlines analysis, improves decision-making speed, boosts productivity, and ensures data consistency and reproducibility.

Conclusion:

In conclusion, this case study focused on analyzing customer patterns and preferences at Danny's Diner, a Japanese food restaurant. The goal was to enhance the customer experience and loyalty program by utilizing data-driven insights.

The study began with an introduction to Danny's Diner and its need for assistance in leveraging the captured data to run the business effectively. The problem statement emphasized Danny's desire to understand customer visiting patterns, expenditure, and favorite menu items to personalize the dining experience and strengthen customer loyalty.

Three key datasets were provided: sales, menu, and members. The sales table captured customer-level purchases with order dates and corresponding product IDs. The menu table mapped product IDs to product names and prices. The members table recorded customer IDs and join dates for the loyalty program.

The questions covered various aspects, such as total customer expenditure, number of days visited, first and most popular menu items, and points earned through the loyalty program.

By answering these questions, Danny's Diner can gain valuable insights into customer behavior, preferences, and loyalty, enabling them to make informed decisions about enhancing the customer experience and expanding the loyalty program.

In summary, through data analysis and SQL queries, Danny's Diner can unlock the potential of their captured data to provide a better dining experience, strengthen customer relationships, and drive business growth.